import { def, isObject, isString } from "@vue/shared"
import { DataSource, EntityMetadata, EntityTarget, ObjectLiteral, QueryBuilder, QueryRunner, SelectQueryBuilder } from "typeorm"
import { keyBy, groupBy } from 'lodash-es'
import { Alias } from "typeorm/query-builder/Alias.d.ts"
import { DriverUtils } from "typeorm/driver/DriverUtils.js"
import { RelationMetadata } from "typeorm/metadata/RelationMetadata.js"
import { ColumnMetadata } from "typeorm/metadata/ColumnMetadata.js"
import { ConstructorParameters, getProp } from "./utils"

type OP = 'contains' | 'endsWith' | 'equals' | 'gt' | 'gte' | 'in' | 'lt' | 'lte' | 'not' | 'notIn' | 'startsWith'

const opMap: Record<OP, (val: any) => string> = {
  contains: p => `LIKE ${p}`,
  startsWith: p => `LIKE ${p}`,
  endsWith: p => `LIKE ${p}`,
  equals: p => `= ${p}`,
  not: p => `!= ${p}`,
  gt: p => `> ${p}`,
  gte: p => `>= ${p}`,
  lt: p => `< ${p}`,
  lte: p => `<= ${p}`,
  in: p => `IN (${p.join(',')})`, 
  notIn: p => `NOT IN (${p.join(',')})`
}

type Val = string | number | boolean
type BasicFilter = Val | FindOper
type Where = Record<string, BasicFilter | ListRelationFilter | Record<string, BasicFilter | ListRelationFilter>>
type ListRelationFilter = { some?: Where; every?: Where; none?: Where }

type FindOper = StringOper | IntOper | BoolOper

type StringOper = {
  contains: string
  startsWith: string
  endsWith: string
  equals: string
  not: string
  gt: string
  gte: string
  lt: string
  lte: string
  in: string[]
  notIn: string[]
}

type IntOper = {
  equals: number
  not: number
  gt: number
  gte: number
  lt: number
  lte: number
  in: number[]
  notIn: number[]
}

type BoolOper = {
  equals: boolean
  not: boolean
}

type FindOpt<T> = {
  select?: Select<T>
  where?: Where
  orderBy?: SortByWith<T>
  take?: number
  skip?: number
}

type Select<T> = {
  [K in keyof T]?: T[K] extends Val
    ? boolean
    : T[K] extends any[]
      ? FindOpt<T[K]>
      : Pick<FindOpt<T[K]>, 'select' | 'where'>
}

type SortOrder = 'asc' | 'desc'
type MSortOrder = { _count: SortOrder }
type SortByWith<T> = {
  [K in keyof T]?: T[K] extends Val
    ? SortOrder
    : T[K] extends any[]
      ? MSortOrder
      : SortByWith<T[K]>
}


class _Find<T extends ObjectLiteral> extends SelectQueryBuilder<any> {

  #options: FindOpt<T> = {}

  constructor(target: EntityTarget<T> | EntityMetadata, opt: FindOpt<T> | undefined, connection: DataSource, queryRunner?: QueryRunner) {
    super(connection, queryRunner)
    const metadata = target instanceof EntityMetadata ? target : connection.getMetadata(target)
    this.from(metadata.name, metadata.tableName)
    this.buildOptions(opt)
  }

  buildOptions(opt?: FindOpt<T>) {
    const mainAlias = this.expressionMap.mainAlias!
    opt = { ...opt }
    opt.select ??= mainAlias.metadata.columns.reduce((o, e) => (o[getProp(e)] = true, o), {} as any)
    this.#options = opt
    this.#select(opt.select!)
    this.skip(opt.skip)
    this.take(opt.take)
    return this
  }

  async find() {
    return (await this.findMany())[0]
  }

  async findMany() {
    const rows = await this.getRawMany()
    if (!rows.length) return rows

    // broadcast all "after load" events
    const mainAlias = this.expressionMap.mainAlias!, metadata = mainAlias.metadata
    // if (expressionMap.callListeners === true && expressionMap.mainAlias!.hasMetadata) {
    //   await queryRunner.broadcaster.broadcast("Load", expressionMap.mainAlias.metadata, entities);
    // }
    const select = this.#options.select!
    const rels = Object.keys(select).filter(k => select[k] && metadata.hasRelationWithPropertyPath(k))
    if (rels.length) {
      const all = rels.map(k => this.#loadRel(metadata.findRelationWithPropertyPath(k)!, rows))
      await Promise.all(all)
    }
    
    return rows
  }

  async #loadRel(rel: RelationMetadata, entities: any[]) {
    const k = rel.propertyName
    const select = this.#options.select!
    const opt = isObject(select[k]) ? select[k] : {}
    const t = rel.inverseEntityMetadata.targetName
    const qb = new _Find(t, opt!, this.connection, this.queryRunner)

    const addSelect = (cols: ColumnMetadata[]) => cols.forEach(col => qb.#addSelect(`${t}.${col.propertyName}`, col.databaseName))

    if (rel.isManyToMany) {
      const midRows = await this.#loadMid(rel, entities)
      const cols1 = rel.isOwning ? rel.junctionEntityMetadata!.ownerColumns : rel.junctionEntityMetadata!.inverseColumns
      const cols2 = rel.isOwning ? rel.junctionEntityMetadata!.inverseColumns : rel.junctionEntityMetadata!.ownerColumns
      addSelect(cols2.map(col => col.referencedColumn!))
      const exp = cols2.map(col => `${col.referencedColumn!.propertyName} IN (${midRows.map(e => qb.createParameter(e[col.propertyName])).join(',')})`).join(' AND ')
      qb.andWhere(exp)
      const rows = await qb.findMany()
      if (cols1.length == 1) {
        const keybyed = keyBy(rows, cols2[0].referencedColumn!.propertyName)
        const grouped = groupBy(midRows, cols1[0].propertyName)
        entities.forEach(e =>
          e[k] = grouped[e[cols1[0].referencedColumn!.propertyName]]?.map(e => keybyed[e[cols2[0].propertyName]]) || []
        )
      } else {
        // todo
      }
    }
    else if (rel.isOneToMany || rel.isOneToOneNotOwner) {
      const cols = rel.inverseRelation!.joinColumns
      addSelect(cols)
      const exp = cols.map(col => `${col!.databaseName} IN (${entities.map(e => qb.createParameter(e[col.referencedColumn!.propertyName])).join(',')})`).join(' AND ')
      qb.andWhere(exp)
      const rows = await qb.findMany()
      if (cols.length == 1) {
        const indexed = (rel.isOneToOne ? keyBy : groupBy)(rows, cols[0].databaseName)
        entities.forEach(e =>
          e[k] = indexed[e[cols[0].referencedColumn!.propertyName]] || (rel.isOneToOne ? null : [])
        )
      } else {
        entities.forEach(e => {
          e[k] = rel.isOneToOne
            ? rows.find(row => cols.every(col => col.compareEntityValue(row, e[col.referencedColumn!.propertyName])))
            : rows.filter(row => cols.every(col => col.compareEntityValue(row, e[col.referencedColumn!.propertyName])))
        })
      }
    }
    else if (rel.isManyToOne || rel.isOneToOneOwner) {
      const cols = rel.joinColumns
      addSelect(cols.map(col => col.referencedColumn!))
      const exp = cols.map(col => `${col.referencedColumn!.propertyName} IN (${entities.map(e => qb.createParameter(e[col.propertyName])).join(',')})`).join(' AND ')
      qb.andWhere(exp)
      const rows = await qb.findMany()
      if (cols.length == 1) {
        const keybyed = keyBy(rows, cols[0].referencedColumn!.propertyName)
        entities.forEach(e =>
          e[k] = keybyed[e[cols[0].propertyName]] || null
        )
      } else {
        entities.forEach(e =>
          e[k] = rows.find(row => cols.every(col => col.compareEntityValue(e, row[col.referencedColumn!.propertyName]))) || null
        )
      }
    }
  }

  #loadMid(rel: RelationMetadata, entities: any[]) {
    if (!entities.length) return [] as any[]
    const t = rel.junctionEntityMetadata!.tableName
    const cols1 = rel.isOwning ? rel.junctionEntityMetadata!.ownerColumns : rel.junctionEntityMetadata!.inverseColumns
    const cols2 = rel.isOwning ? rel.junctionEntityMetadata!.inverseColumns : rel.junctionEntityMetadata!.ownerColumns
    const qb = new _Find(t, undefined, this.connection, this.queryRunner)
    cols1.forEach(col => qb.addSelect(`${t}.${col.propertyName}`, col.propertyName))
    cols2.forEach(col => qb.addSelect(`${t}.${col.propertyName}`, col.propertyName))
    const exp = cols1.map(col => `${col.propertyName} IN (${entities.map(e => qb.createParameter(e[col.referencedColumn!.propertyName])).join(',')})`).join(' AND ')
    qb.where(exp)
    return qb.getRawMany()
  }

  getQuery() {
    const sql1 = 
      this.createComment() + 
      this.createCteExpression() + 
      this.createSelectExpression()

    const sql3 = 
      this.createWhereExpression() + 
      this.createGroupByExpression() +
      this.createHavingExpression() + 
      this.createOrderByExpression() + 
      this.createLimitOffsetExpression() + 
      this.createLockExpression()
    
    const sql2 = 
      this.createJoinExpression()
    
    let sql = sql1 + sql2 + sql3
    if (this.expressionMap.subQuery)
        sql = "(" + sql + ")";
    
    return this.replacePropertyNamesForTheWholeQuery(sql);
  }

  #subQuery(table: string) {
    const qb = new _Find(table, undefined, this.connection, this.queryRunner)
    qb.expressionMap.subQuery = true
    qb.parentQueryBuilder = this
    return qb
  }

  #addSelect(name: string, alias: string) {
    if (this.expressionMap.selects.find(e => e.aliasName == alias)) return
    this.expressionMap.selects.push({ selection: name, aliasName: alias })
  }

  #leftJoin(name: string, alias: string, condition?: string, parameters?: ObjectLiteral) {
    if (this.expressionMap.joinAttributes.find(e => e.alias.name == alias)) return
    this.leftJoin(name, alias, condition, parameters)
  }

  #select(obj: Select<T>) {
    const t = this.expressionMap.mainAlias!.name
    const selects = this.expressionMap.selects ||= []
    const { metadata } = this.expressionMap.mainAlias!
    for (const k in obj) {
      const rel = metadata.findRelationWithPropertyPath(k)
      const val = obj[k]
      if (rel && val) {
        if (rel.isManyToMany) {
          const cols = rel.isOwning ? rel.junctionEntityMetadata!.ownerColumns : rel.junctionEntityMetadata!.inverseColumns
          cols.forEach(col => {
            selects.push({ selection: `${t}.${col.referencedColumn!.propertyName}`, aliasName: col.referencedColumn!.propertyName })
          })
        }
        else if (rel.isOneToMany || rel.isOneToOneNotOwner) {
          rel.inverseRelation!.joinColumns.forEach(col => {
            selects.push({ selection: `${t}.${col.referencedColumn!.propertyName}`, aliasName: col.referencedColumn!.propertyName })
          })
        }
        else if (rel.isManyToOne || rel.isOneToOneOwner) {
          rel.joinColumns.forEach(col => {
            selects.push({ selection: `${t}.${col.propertyName}`, aliasName: col.propertyName })
          })
        }
      }
      else if (val) {
        selects.push({ selection: `${t}.${k}`, aliasName: k })
      }
      // DriverUtils.buildAlias()
    }
  }

  createWhereExpression() {
    const alias = this.expressionMap.mainAlias!
    const exp = this.#buildWhereExp(this, this.#options.where!, alias.metadata, alias.name)
    return exp ? ` WHERE ${exp}` : ``
  }

  createOrderByExpression() {
    const createExp = (orderBy: SortByWith<any> | undefined, metadata: EntityMetadata, alias?: string) => {
      if (!orderBy) return ""
      let exp = ''
      for (const k in orderBy) {
        const col = metadata.findColumnWithPropertyPathStrict(k)
        const rel = metadata.findRelationWithPropertyPath(k)
        const order = orderBy[k]
        if (col) {
          const _k = alias ? `${alias}.${k}` : k
          if (order) exp += `, ${_k} ${(order as string).toUpperCase()}`
          // else todo throw
        }
        else if (rel) {
          if (rel.isOneToOne || rel.isManyToOne) {
            const joinAlias = `${alias}@${k}`
            this.#leftJoin(`${alias}.${k}`, joinAlias)
            exp += `, ` + createExp(order as SortByWith<any>, rel.inverseEntityMetadata, joinAlias)
          }
          else {
            const _count = (order as MSortOrder)._count
            if (_count) {
              const joinAlias = `${alias}^${k}`
              const tableName = rel.isManyToMany ? rel.junctionEntityMetadata!.tableName : rel.inverseEntityMetadata.tableName
              // todo joinColumns > 1
              const col = rel.isManyToMany ? (rel.isOwning ? rel.junctionEntityMetadata!.ownerColumns[0] : rel.junctionEntityMetadata!.inverseColumns[0]) : rel.inverseRelation!.joinColumns[0]
              const on = `"${joinAlias}".${col!.databaseName} = ${alias}.${col.referencedColumn!.propertyName}`
              this.#leftJoin(`(SELECT ${col!.databaseName}, COUNT(*) AS "#count" FROM ${tableName} GROUP BY ${col!.databaseName})`, joinAlias, on)
              exp += `, "${joinAlias}"."#count" ${_count.toUpperCase()}`
            }
          }
        }
      }
      if (exp) exp = exp.slice(2)
      return exp
    }
    const { metadata, name } = this.expressionMap.mainAlias!
    const exp = createExp(this.#options.orderBy, metadata, name)
    return exp ? ` ORDER BY ${exp}` : ''
  }

  // can't support `embed`
  #buildWhereExp(sqb: _Find<any>, where: Where | undefined, metadata: EntityMetadata, alias: string) {
    let exp = ''
    for (const k in where) {
      const col = metadata.findColumnWithPropertyPathStrict(k)
      const rel = metadata.findRelationWithPropertyPath(k)
      const path = `${alias}.${k}`
      const val = where[k]
      if (col) {
        if (isObject(val)) {
          const findOper = val as Record<OP, any>
          for (const op in findOper) {
            const opVal = findOper[op]
            let param
            switch (op) {
              case 'contains': param = this.createParameter(`%${opVal}%`); break
              case 'startsWith': param = this.createParameter(`%${opVal}`); break
              case 'endsWith':  param = this.createParameter(`${opVal}%`); break
              case 'in':
              case 'notIn': param = opVal.map(e => this.createParameter(e)); break
              default: param = this.createParameter(opVal)
            }
            exp += ` AND ${path} ${opMap[op](param)}`
          }
        }
        else if (val !== undefined) {
          exp += ` AND ${path} = ${this.createParameter(val)}`
        }
      }
      else if (rel) {
        if (rel.isManyToMany || rel.isOneToMany) {
          const t = rel.isManyToMany ? rel.junctionEntityMetadata!.tableName : rel.inverseEntityMetadata.tableName
          // todo joinColumns > 1
          const col = rel.isManyToMany ? (rel.isOwning ? rel.joinColumns[0] : rel.inverseRelation!.inverseJoinColumns[0]) : rel.inverseRelation!.joinColumns[0]
          const refer = `${alias}.${col.referencedColumn!.propertyName}`
          const sub = sqb.#subQuery(t)
          sub.select(`${t}.${col.propertyName}`)
          const findOper = val as ListRelationFilter
          // sub.where(`${refer} = ${joinTableName}.${joinColumn.propertyName}`)
          if (findOper.some) {
            // sub.andWhere(this.#buildWhereExp(sub, findOper.some, rel.inverseEntityMetadata, t))
            // sqb.andWhere(refer + ' IN ' + sub.getSql())
            sub.#options.where = findOper.some
            exp += ` AND ${refer} IN ${sub.getQuery()}`
          }
          if (findOper.every) {
            // todo
          }
          if (findOper.none) {
            // todo
          }
        }
        else {
          // todo 1-1 or n-
          const joinAlias = alias + '@' + rel.propertyName
          const relExp = this.#buildWhereExp(sqb, val as Where, rel.inverseEntityMetadata, joinAlias)
          if (relExp) {
            sqb.#leftJoin(`${alias}.${rel.propertyName}`, joinAlias)
            exp += ` AND (${relExp})`
          }
        }
      }
      else {
        switch (k) {
          case 'AND':
            // todo
            break
          case 'OR':
            // todo
            break
          default:
            throw new Error(`Unknown argument 'where.${k}'.`)
        }
      }
    }
    if (exp) exp = exp.slice(5)
    return exp
  }

  #raw2entity(raws: any[], alias: Alias) {

  }
}

export const Find = (...arg: ConstructorParameters<typeof _Find>) => {
  return new _Find(...arg).find()
}

export const FindMany = (...arg: ConstructorParameters<typeof _Find>) => {
  return new _Find(...arg).findMany()
}